


ALTER      proc sp_fab_stage_yield_report
-- Input parameter is process StepID;
@process_step_id int,
@process_step_type varchar(15),
@location_Id int,
@date_From varchar(10),
@date_To   varchar(10)


as

/*variable to store the values
*/
declare @qty_in int, @qty_out int, @yield float
declare @date_in datetime, @date_out datetime
declare @temp_lot_id int


declare processStepCursor CURSOR FOR
select * from fn_get_process_flow_by_id (@process_step_id) where Process_Type_Name='FAB'

declare @@temp_process_step_id bigint
declare @@temp_ps_type varchar(20)
declare @@temp_ps_type_name varchar(20)
declare @results Table(Lot_ID varchar(20),Location varchar(50),Product varchar(50),Process_Name varchar(20), Qty_in int, Qty_out int,Yield int, Date_in datetime, Date_out datetime)
open processStepCursor 
fetch next from processStepCursor into @@temp_process_step_id,@@temp_ps_type,@@temp_ps_type_name

while @@fetch_status = 0
begin
-- Cursor to store the Lot_Number and Lot_ID
-- If process_step_id variable is 0 store all lot_numbers and 
declare @lotCursor CURSOR
if(@location_Id='0')
begin
set @lotCursor = CURSOR for
select Lot_Table.Lot_ID, Lot_Number,Stage_ID,SubStage_ID from Lot_Table,Fab_Stage_Lot where ProcessStep_ID = @@temp_process_step_id and Lot_Table.Lot_ID=Fab_Stage_Lot.Lot_ID
end

else
begin
set @lotCursor = CURSOR for
select Lot_Table.Lot_ID, Lot_Number,Stage_ID,SubStage_ID from Lot_Table,Fab_Stage_Lot where ProcessStep_ID = @@temp_process_step_id and Lot_Table.location_Id=@location_Id  and  Lot_Table.Lot_ID=Fab_Stage_Lot.Lot_ID
end

fetch next from processStepCursor into @@temp_process_step_id,@@temp_ps_type,@@temp_ps_type_name
end
close processStepCursor
deallocate processStepCursor

declare @stage_id bigint
declare @stage_name varchar(30)
declare @sub_stage_id bigint
declare @lot_stage_id bigint
declare @lot_sub_stage_id bigint
declare @lot_number varchar(30)
declare @location_name varchar(30)
declare @product_name varchar(30)
declare @lot_id bigint

open @lotCursor 
fetch next from @lotCursor
into @lot_id,@lot_number,@lot_stage_id,@lot_sub_stage_id
while @@fetch_status = 0
begin

if(@lot_stage_id is not null)
begin
if(@date_From='0' and @date_To='0')
begin
select @qty_in=FSL_Qty_In,@qty_out=FSL_Qty_Out,@date_in=FSL_Date_In,@date_out=FSL_Date_Out,@yield=FSL_Yield,@lot_sub_stage_id=SubStage_ID from Fab_Stage_Lot where lot_id = @lot_id and Stage_ID = @lot_stage_id  
select @location_name=location.L_Vendor_Name,@product_name=Part_Number from Lot_Table,Location,Product where Lot_Table.Lot_ID=@lot_id and  Lot_Table.Location_ID=Location.Location_ID and Lot_Table.ProcessStep_ID=Product.ProcessStep_ID
select @stage_name=FST_Stage_Name from FAB_Stage_Table where Stage_ID = @lot_stage_id
insert into @results(Lot_ID,Location,Product,Process_Name, Qty_in, Qty_out,Yield,Date_in,Date_out) values(@lot_number,@location_name,@product_name,@stage_name, @qty_in,@qty_out,@yield,@date_in,@date_out) 
end
else
begin
select @qty_in=FSL_Qty_In,@qty_out=FSL_Qty_Out,@date_in=FSL_Date_In,@date_out=FSL_Date_Out,@yield=FSL_Yield,@lot_sub_stage_id=SubStage_ID from Fab_Stage_Lot where lot_id = @lot_id and Stage_ID = @lot_stage_id  and FSL_Date_Out>=@date_From and FSL_Date_In<=@date_To
select @location_name=location.L_Vendor_Name,@product_name=Part_Number from Lot_Table,Location,Product where Lot_Table.Lot_ID=@lot_id and  Lot_Table.Location_ID=Location.Location_ID and Lot_Table.ProcessStep_ID=Product.ProcessStep_ID
select @stage_name=FST_Stage_Name from FAB_Stage_Table where Stage_ID = @lot_stage_id
insert into @results(Lot_ID,Location,Product,Process_Name, Qty_in, Qty_out,Yield, Date_in, Date_out) values(@lot_number,@location_name,@product_name,@stage_name, @qty_in,@qty_out,@yield,@date_in, @date_out) 
end
end

if(@lot_sub_stage_id is not null)
begin
if(@date_From='0' and @date_To='0')
begin
select @qty_in=FSL_Qty_In,@qty_out=FSL_Qty_Out,@date_in=FSL_Date_In,@date_out=FSL_Date_Out,@yield=FSL_Yield,@lot_sub_stage_id=SubStage_ID from Fab_Stage_Lot where lot_id = @lot_id and SubStage_ID = @lot_sub_stage_id  
select @location_name=location.L_Vendor_Name,@product_name=Part_Number from Lot_Table,Location,Product where Lot_Table.Lot_ID=@lot_id and  Lot_Table.Location_ID=Location.Location_ID and Lot_Table.ProcessStep_ID=Product.ProcessStep_ID
select @stage_name=FSST_Sub_Stage_Name from FAB_Sub_Stage_Table where SubStage_ID = @lot_sub_stage_id
insert into @results(Lot_ID,Location,Product,Process_Name, Qty_in, Qty_out,Yield,Date_in, Date_out) values(@lot_number,@location_name,@product_name,@stage_name, @qty_in,@qty_out,@yield,@date_in,@date_out) 
end
else
begin
select @qty_in=FSL_Qty_In,@qty_out=FSL_Qty_Out,@date_in=FSL_Date_In,@date_out=FSL_Date_Out,@yield=FSL_Yield,@lot_sub_stage_id=SubStage_ID from Fab_Stage_Lot where lot_id = @lot_id and SubStage_ID = @lot_sub_stage_id  and FSL_Date_Out>=@date_From and FSL_Date_In<=@date_To
select @location_name=location.L_Vendor_Name,@product_name=Part_Number from Lot_Table,Location,Product where Lot_Table.Lot_ID=@lot_id and  Lot_Table.Location_ID=Location.Location_ID and Lot_Table.ProcessStep_ID=Product.ProcessStep_ID
select @stage_name=FSST_Sub_Stage_Name from FAB_Sub_Stage_Table where SubStage_ID = @lot_sub_stage_id
insert into @results(Lot_ID,Location,Product,Process_Name, Qty_in, Qty_out,Yield,Date_in, Date_out) values(@lot_number,@location_name,@product_name,@stage_name, @qty_in,@qty_out,@yield,@date_in,@date_out) 
end
end
fetch next from @lotCursor
into @lot_id,@lot_number,@lot_stage_id,@lot_sub_stage_id
end
close @lotCursor
deallocate @lotCursor
select distinct Lot_ID,Location,Product,Process_Name,Qty_in,Qty_out,Yield,Date_in,Date_out from @results 

